﻿using System;
using System.Text;
using System.Data;
using System.Collections.Generic;

using MySql.Data.MySqlClient;
using Think.Lenovo.Com.Models;
using Think.Lenovo.Com.Common;

namespace Think.Lenovo.Com.DataAccess
{
    public class DataReport
    {
        public long AddDataReport(DataReportModel model)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;

            //sql语句
            StringBuilder sql = new StringBuilder(128);
            sql.Append("insert into ")
                .Append("tbl_datareporttemplate ( ReportName, ReportPath, CreateUserId, CreateDate, ModifyDate, ReportCollectFolder,Description)")
                .Append("values(@ReportName, @ReportPath, @CreateUserId, @CreateDate, @ModifyDate,@ReportCollectFolder,@Description)");

            MySqlParameter[] ps = new MySqlParameter[] {
                    new MySqlParameter("@ReportName",MySqlDbType.VarChar,100),
                    new MySqlParameter("@ReportPath",MySqlDbType.VarChar,1024),
                    new MySqlParameter("@CreateUserId",MySqlDbType.Int32),
                    new MySqlParameter("@CreateDate",MySqlDbType.DateTime),
                    new MySqlParameter("@ModifyDate",MySqlDbType.DateTime),
                    new MySqlParameter("@ReportCollectFolder",MySqlDbType.VarChar,1024),
                    new MySqlParameter("@Description",MySqlDbType.Text)
                };

            ps[0].Value = model.ReportName;
            ps[1].Value = model.ReportPath;
            ps[2].Value = model.CreateUserId;
            ps[3].Value = model.CreateDate;
            ps[4].Value = model.ModifyDate;
            ps[5].Value = model.ReportCollectFolder;
            ps[6].Value = model.Description;
            long id = MysqlHelper.Insert(conn, sql.ToString(), ps);
            return id;
        }
        /// <summary>
        /// 得到一页数据
        /// </summary>
        /// <param name="pageIndex">页号</param>
        /// <param name="pageSize">每页数据数量</param>
        /// <param name="conditions">查询条件</param>
        /// <returns></returns>
        public LinkedList<DataReportModel> GetDataReports(int pageIndex, int pageSize, String conditions)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(512);
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (pageSize < 1)
            {
                pageSize = 10;
            }
            sql.Append("Select * from (")
                .Append(" Select A.*,B.realName from tbl_DataReportTemplate As A inner join tbl_userInfo As B on A.createUserId=B.userId ")
                .Append(") As Tbl ");
            if (!String.IsNullOrEmpty(conditions))
            {
                sql.Append(" Where " + conditions);
            }
            sql.Append(" order by Id desc ");
            sql.Append(" limit ")
            .Append((pageIndex - 1) * pageSize)
            .Append(",")
            .Append(pageSize);
            DataSet ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            LinkedList<DataReportModel> list = new LinkedList<DataReportModel>();
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    foreach (DataRow row in ds.Tables[0].Rows)
                    {
                        DateTime dt;
                        Int32 i;
                        DataReportModel model = new DataReportModel();
                        model.Id = Convert.ToInt32(row["Id"]);
                        DateTime.TryParse(row["createDate"].ToString(), out dt);
                        model.CreateDate = dt;
                        Int32.TryParse(row["CreateUserId"].ToString(), out i);
                        model.CreateUserId = i;
                        DateTime.TryParse(row["ModifyDate"].ToString(), out dt);
                        model.ModifyDate = dt;
                        model.ReportCollectFolder = Convert.ToString(row["ReportCollectFolder"]);
                        model.ReportName = Convert.ToString(row["ReportName"]);
                        model.ReportPath = Convert.ToString(row["ReportPath"]);
                        model.Description = Convert.ToString(row["Description"]);
                        model.CreateUserName = Convert.ToString(row["realName"]);
                        list.AddLast(model);
                    }
                }
            }
            return list;
        }

        public DataSet GetDataReportsBy(int pageIndex, int pageSize, String conditions)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(512);
            if (pageIndex < 1)
            {
                pageIndex = 1;
            }
            if (pageSize < 1)
            {
                pageSize = 10;
            }
            sql.Append("select TblA.*,TblB.FilePath,TblB.Id As DataReportId,TblB.uploadDate,TblB.userId As uploadUserId from ")
                .Append("(Select A.*,B.realName from tbl_DataReportTemplate As A inner join tbl_userInfo As B on A.createUserId=B.userId) As TblA left join tbl_userdatareportinfo As TblB")
                .Append(" on TblA.Id = TblB.templateId");
            if (!String.IsNullOrEmpty(conditions))
            {
                sql.Append(" Where " + conditions);
            }
            sql.Append(" order by Id desc ");
            sql.Append(" limit ")
            .Append((pageIndex - 1) * pageSize)
            .Append(",")
            .Append(pageSize);
            DataSet ds = MySqlHelper.ExecuteDataset(conn, sql.ToString());
            return ds;
        }

        public int GetDataReportCountBy(String conditions)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            StringBuilder sql = new StringBuilder(256);
            sql.Append("select Count(TblA.Id) from ")
                .Append("(Select A.*,B.realName from tbl_DataReportTemplate As A inner join tbl_userInfo As B on A.createUserId=B.userId) As TblA left join tbl_userdatareportinfo As TblB")
                .Append(" on TblA.Id = TblB.templateId ");
            if (!String.IsNullOrEmpty(conditions))
            {
                sql.Append(" Where "+conditions);
            }
            Object obj = MysqlHelper.ExecuteScalar(conn, sql.ToString());
            return obj == null ? 0 : Convert.ToInt32(obj);
        }


        /// <summary>
        /// 执行一条Sql查询语句,返回数据报表集合
        /// </summary>
        /// <param name="sql">参数化的Sql语句</param>
        /// <param name="ps">参数数组</param>
        /// <returns>LinkedList<DataReportModel>集合</returns>
        public LinkedList<DataReportModel> GetDataReports(String sql, MySql.Data.MySqlClient.MySqlParameter[] ps)
        {
             String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
             DataSet ds = MysqlHelper.Query(conn, sql, ps);
             LinkedList<DataReportModel> list = new LinkedList<DataReportModel>();
             if (ds != null)
             {
                 if (ds.Tables.Count > 0)
                 {
                     foreach (DataRow row in ds.Tables[0].Rows)
                     {
                         DateTime dt;
                         Int32 i;
                         DataReportModel model = new DataReportModel();
                         model.Id = Convert.ToInt32(row["Id"]);
                         DateTime.TryParse(row["createDate"].ToString(), out dt);
                         model.CreateDate = dt;
                         Int32.TryParse(row["CreateUserId"].ToString(), out i);
                         model.CreateUserId = i;
                         DateTime.TryParse(row["ModifyDate"].ToString(), out dt);
                         model.ModifyDate = dt;
                         model.ReportCollectFolder = Convert.ToString(row["ReportCollectFolder"]);
                         model.ReportName = Convert.ToString(row["ReportName"]);
                         model.ReportPath = Convert.ToString(row["ReportPath"]);
                         model.Description = Convert.ToString(row["Description"]);
                         list.AddLast(model);
                     }
                 }
             }
            return list;
        }
        /// <summary>
        /// 得到一个DataReportModel对象
        /// </summary>
        /// <param name="id">模板id</param>
        /// <returns>DataReportModel实例</returns>
        public DataReportModel GetDataReport(int id)
        {
            StringBuilder sql = new StringBuilder(128);
            sql.Append("select * from tbl_datareporttemplate Where Id="+id);
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            DataSet ds = MysqlHelper.Query(conn, sql.ToString());
            DataReportModel model = new DataReportModel();
            if (ds != null)
            {
                if (ds.Tables.Count > 0)
                {
                    if(ds.Tables[0].Rows.Count>0)
                    {
                        DateTime dt;
                        Int32 i;   
                        model.Id = Convert.ToInt32(ds.Tables[0].Rows[0]["Id"]);
                        DateTime.TryParse(ds.Tables[0].Rows[0]["createDate"].ToString(), out dt);
                        model.CreateDate = dt;
                        Int32.TryParse(ds.Tables[0].Rows[0]["CreateUserId"].ToString(), out i);
                        model.CreateUserId = i;
                        DateTime.TryParse(ds.Tables[0].Rows[0]["ModifyDate"].ToString(), out dt);
                        model.ModifyDate = dt;
                        model.ReportCollectFolder = Convert.ToString(ds.Tables[0].Rows[0]["ReportCollectFolder"]);
                        model.ReportName = Convert.ToString(ds.Tables[0].Rows[0]["ReportName"]);
                        model.ReportPath = Convert.ToString(ds.Tables[0].Rows[0]["ReportPath"]);
                        model.Description = Convert.ToString(ds.Tables[0].Rows[0]["Description"]);
                    }
                }
            }
            return model;
        }
        /// <summary>
        /// 得到数据模板的个数
        /// </summary>
        /// <param name="sql">查询sql语句</param>
        /// <param name="ps">sql参数组组</param>
        /// <returns>返回结果的第一行第一列的值</returns>
        public int GetDataReportCount(String sql, MySqlParameter[] ps)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            Object  obj = MysqlHelper.ExecuteScalar(conn, sql,ps);
            int result = 0;
            if (Int32.TryParse(obj.ToString(), out result))
            {
                return result;
            }
            else {
                return 0;
            }
        }

        public int DeleteReport(int id)
        {
            String conn = System.Configuration.ConfigurationManager.ConnectionStrings["MySqlHost"].ConnectionString;
            String sql = "Delete From tbl_datareporttemplate Where Id=" + id;
            int affect = MysqlHelper.Delete(conn, sql, null);
            return affect;
        }
    }
}
